package com.orange.studio.dbk.dao;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

import com.orange.studio.dbk.dto.SearchDTO;
import com.orange.studio.dbk.dto.SongDTO;
import com.orange.studio.dbk.utils.AppConfig;
import com.orange.studio.dbk.utils.AppConfig.SEARCH_TYPE;
import com.orange.studio.dbk.utils.DataBaseHelper2;
import com.orange.studio.dbk.utils.DatabaseHelper;

public class SongDAO {
	private static final String TAG="SongDAO";
	//Database
	private SQLiteDatabase database;
	private DataBaseHelper2 dbHelper;
	private String[] allColumns;
	
	public SongDAO(Context context){
		dbHelper=new DataBaseHelper2(context);
		try {
			dbHelper.createDataBase();
		} catch (IOException e) {
			e.printStackTrace();
		}
		allColumns=new String[]{
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_ID,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_NAME,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_NAMECLEAN,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_ABBR,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_MANUFACTURE,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LANGUAGE,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LYRIC,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LYRIC_CLEAN,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_META,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_MEACLEAN,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LIKED,
			DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_VOL,
		};		
	}
	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {		
		try {
			dbHelper.close();
		} catch (SQLiteException e) {
		}
	}
	public SongDTO create(SongDTO record) throws SQLiteException {
		SongDTO song=null;
		ContentValues content=new ContentValues();
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_ID, record.getId());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_NAME, record.getName());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_NAMECLEAN, record.getNameclean());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_ABBR, record.getAbbr());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_MANUFACTURE, record.getManufacture());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LANGUAGE, record.getLanguage());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LYRIC, record.getLyric());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LYRIC_CLEAN, record.getLyricclean());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_META, record.getMeta());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_MEACLEAN, record.getMetaclean());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LIKED, record.getLiked());
		content.put(DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_VOL, record.getVol());
		long insertId=database.insert(DatabaseHelper.TABLE_LIST_SONGS, null, content);
		String  where=DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_ID+" = "+insertId;
		
		Cursor cursor=database.query(DatabaseHelper.TABLE_LIST_SONGS, allColumns, where, null,null, null, null);
		cursor.moveToFirst();
		if(!cursor.isAfterLast()){
			song=cursorToSongDTO(cursor);
		}
		cursor.close();
		return song;
	}
	public ArrayList<SongDTO> getListSongs(List<Integer> ids) throws SQLiteException {
		if(ids==null || ids.size()<1){
			return null;
		}
		ArrayList<SongDTO> records = new ArrayList<SongDTO>();
		//String limit = AppConfig.OFFSET * (page - 1) + ", " + AppConfig.OFFSET;
		String where=" _id in (";
		for (int i = 0; i < ids.size(); i++) {
			if(i==ids.size()-1){
				where+=ids.get(i).toString();
			}
			else{
				where+=ids.get(i).toString()+",";
			}
		}
		where+=")";
		Cursor cursor = database.query(DatabaseHelper.TABLE_LIST_SONGS, 
				allColumns, where, null, null, null,
				DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_NAME + " ASC", null);

		cursor.moveToFirst();
		SongDTO tmpRecord;
		while (!cursor.isAfterLast()) {
			tmpRecord = cursorToSongDTO(cursor);
			records.add(tmpRecord);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return records;
	}
	public ArrayList<SongDTO> getAll(int typeNumber,int page) throws SQLiteException {
		ArrayList<SongDTO> records = new ArrayList<SongDTO>();
		String limit = AppConfig.OFFSET * (page - 1) + ", " + AppConfig.OFFSET;
		String where="";
		if(typeNumber==1 || typeNumber==2){
			where=DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_MANUFACTURE+" ="+typeNumber;
		}else{
			where=null;
		}
		Cursor cursor = database.query(DatabaseHelper.TABLE_LIST_SONGS, 
				allColumns, where, null, null, null,
				DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_ID + " ASC", limit);

		cursor.moveToFirst();
		SongDTO tmpRecord;
		while (!cursor.isAfterLast()) {
			tmpRecord = cursorToSongDTO(cursor);
			records.add(tmpRecord);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return records;
	}
	public boolean updateSongLiked(int id, boolean isLiked){
		boolean result=false;
		int liked=(isLiked?1:0);
		try {
			String sqlQuery="update song set sliked="+liked+ " where _id="+id;
			database.execSQL(sqlQuery);
			result=true;
		} catch (Exception e) {
			result=false;
		}
		return result;
	}
	public ArrayList<SongDTO> getFavioritesSongs(int page) throws SQLiteException {
		ArrayList<SongDTO> records = new ArrayList<SongDTO>();
		String limit = AppConfig.OFFSET * (page - 1) + ", " + AppConfig.OFFSET;
		String where="sliked=1";
		Cursor cursor = database.query(DatabaseHelper.TABLE_LIST_SONGS, 
				allColumns, where, null, null, null,
				DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_ID + " ASC", limit);

		cursor.moveToFirst();
		SongDTO tmpRecord;
		while (!cursor.isAfterLast()) {
			tmpRecord = cursorToSongDTO(cursor);
			records.add(tmpRecord);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return records;
	}
	public ArrayList<SongDTO> searchSongs(SearchDTO search,int page) throws SQLiteException {
		ArrayList<SongDTO> records = new ArrayList<SongDTO>();
		String limit = AppConfig.OFFSET * (page - 1) + ", " + AppConfig.OFFSET;
		String where="";
		String manufacture="";
		String vol="";
		String language="";
		//1:5so - 2:6so
		if(search.manufacture>0){
			manufacture=" and " + DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_MANUFACTURE+" ="+search.manufacture;
		}else{
			manufacture="";
		}
		
		if(search.vol>0){
			vol=" and " + DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_VOL+" ="+search.vol;
		}else{
			vol="";
		}
		
		if(search.language>0){
			if(search.language==1){//vn
				language=" and " + DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LANGUAGE+" ='vn'";
			}else{ //en
				language=" and " + DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_LANGUAGE+" ='en'";
			}
		}else{
			language="";
		}
		
		switch (search.type) {
		case SEARCH_TYPE.SEARCH_TYPE_ID:
			where=DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_ID+" = "+search.key+manufacture+vol+language;
			break;
		case SEARCH_TYPE.SEARCH_TYPE_SINGER:
			where=DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_MEACLEAN+" = "+search.key+manufacture+vol+language;
			break;
		case SEARCH_TYPE.SEARCH_TYPE_SONG_NAME:
			where=DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_NAMECLEAN+" like '"+search.key+"%'"+manufacture+vol+language;
			break;
		case SEARCH_TYPE.SEARCH_TYPE_SONG_CONTENT:
			where=DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_MEACLEAN+" = like '%"+search.key+"%' "+manufacture+vol+language;
			break;
		default:
			where= "1=1"+manufacture+vol+language;
			break;
		}
		Cursor cursor = database.query(DatabaseHelper.TABLE_LIST_SONGS, 
				allColumns, where, null, null, null,
				DatabaseHelper.TABLE_SONG_RECORDS_COLUMN_NAME + " ASC", limit);

		cursor.moveToFirst();
		SongDTO tmpRecord;
		while (!cursor.isAfterLast()) {
			tmpRecord = cursorToSongDTO(cursor);
			records.add(tmpRecord);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return records;
	}
	public int total() throws SQLiteException {
		int result = 0;
		Cursor cursor = database.query(DatabaseHelper.TABLE_LIST_SONGS,
				new String[] { "count(*)" }, null, null, null, null, null);

		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			result = cursor.getInt(0);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}
	private SongDTO cursorToSongDTO(Cursor cursor){
		SongDTO song=new SongDTO();
		int i=0;
		song.setId(cursor.getInt(i++));
		song.setName(cursor.getString(i++));
		song.setNameclean(cursor.getString(i++));
		song.setAbbr(cursor.getString(i++));
		song.setManufacture(cursor.getInt(i++));
		song.setLanguage(cursor.getString(i++));
		song.setLyric(cursor.getString(i++));
		song.setLyricclean(cursor.getString(i++));
		song.setMeta(cursor.getString(i++));
		song.setMetaclean(cursor.getString(i++));
		song.setLiked(cursor.getInt(i++));
		song.setVol(cursor.getInt(i++));
		return song;
	}
}
